/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.thienloc.omap.jdbc.dao;

import com.thienloc.omap.util.Constant;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import oracle.jdbc.pool.OracleDataSource;

/**
 *
 * @author Pham Quang Kiem
 */
public class DbAccess {

    private OracleDataSource oracleDataSource = null;
    private static DbAccess instance = null;
    private static final Logger logger = LoggerFactory.getLogger(DbAccess.class);
    private static boolean reload = false;
    private Properties config;

    public DbAccess() {
        try {
            File configFile = new File(Constant.CONFIG_FOLDER + Constant.CONFIG_FILE_NAME);
            config = new Properties();
            config.load(new FileReader(configFile));
//            oracleDataSource = setupDataSource();
        } catch (IOException ex) {
            logger.error("", ex);
        }
    }

    public synchronized static DbAccess getInstance() {
        if (reload) {
            instance = new DbAccess();
            reload = false;

        } else if (instance == null) {
            instance = new DbAccess();
        }

        return instance;
    }

    public Connection getConn() throws SQLException {
        Connection conn = null;
        if (oracleDataSource != null) {
            conn = oracleDataSource.getConnection();
            if (conn != null && !conn.isClosed()) {
                conn.setAutoCommit(isAutocommit());
            }
        } else {
            oracleDataSource = setupDataSource();
        }
        return conn;
    }

    public static void closeConnection(Connection cn) {
        if (cn != null) {
            try {
                if (!cn.isClosed()) {
                    cn.close();
                }
            } catch (SQLException ex) {
                logger.error("", ex);
            }
        }
    }

    public static void commitTransaction(Connection cn) {
        try {
            cn.commit();
        } catch (SQLException ex) {
            logger.error("", ex);
        }
    }

    public static void rollBackTransaction(Connection cn) {
        try {
            cn.rollback();
        } catch (SQLException ex) {
            logger.error("", ex);
        }
    }

    public static void closeConn(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception ex) {
                logger.error("", ex);
            }
        }
    }

    public static void closePreparedStatement(PreparedStatement ps) {
        if (ps != null) {
            try {
                ps.close();
            } catch (Exception ex) {
                logger.error("", ex);
            }
        }
    }

    public static void closeCallableStatement(CallableStatement cs) {
        if (cs != null) {
            try {
                cs.close();
            } catch (Exception ex) {
                logger.error("", ex);
            }
        }
    }

    public static void closeResultSet(ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException ex) {
                logger.error("", ex);
            }
        }
    }

    public static void closeStatement(Statement stmt) {
        if (stmt != null) {
            try {
                stmt.close();
            } catch (Exception ex) {
                logger.error("", ex);
            }
        }
    }

    protected boolean isAutocommit() {
        String autoCommit = config.getProperty("jdbc.autoCommit", "false");
        return autoCommit.equalsIgnoreCase("TRUE");
    }

    private OracleDataSource setupDataSource() {
        OracleDataSource result = null;
        try {
            String url = config.getProperty("jdbc.url");
            String user = config.getProperty("jdbc.username");
            String pass = config.getProperty("jdbc.password");
            result = new OracleDataSource();
            result.setURL(url);
            result.setUser(user);
            result.setPassword(pass);
            result.setImplicitCachingEnabled(true);
            result.setConnectionCachingEnabled(true);
            result.setFastConnectionFailoverEnabled(false);
            Properties cachedProps = new Properties();
            cachedProps.setProperty("MinLimit", config.getProperty("jdbc.minLimit", "1")); // the cache size is 1 at least
            cachedProps.setProperty("MaxLimit", config.getProperty("jdbc.maxLimit", "5"));
            cachedProps.setProperty("InitialLimit", config.getProperty("jdbc.initialLimit", "3")); // create 3 connections at startup
            cachedProps.setProperty("InactivityTimeout", config.getProperty("jdbc.inactivityTimeout", "600")); // seconds
            cachedProps.setProperty("ConnectionWaitTimeout", config.getProperty("jdbc.connectionWaitTimeOut", "20")); // seconds
            cachedProps.setProperty("AbandonedConnectionTimeout", config.getProperty("jdbc.abandonedConnectionTimeout", "300"));
            cachedProps.setProperty("PropertyCheckInterval", config.getProperty("jdbc.propertyCheckInterval", "60")); // seconds
            cachedProps.setProperty("MaxStatementsLimit", config.getProperty("jdbc.maxStatementsLimit", "10"));
            result.setConnectionCacheProperties(cachedProps);

            logger.info("Database ==> Logon with OracleDataSource");
        } catch (Exception ex) {
            logger.error("Error while setting up data source", ex);
        }
        return result;
    }

    public OracleDataSource getOracleDataSource() {
        return oracleDataSource;
    }

    public void setOracleDataSource(OracleDataSource oracleDataSource) {
        this.oracleDataSource = oracleDataSource;
    }
}
